--exec SP_CREATE_UPDATE_TEST_SOFT_HARD_BIN_INFO 'IK52551B-FX-P-03-QL1-L','U8475285.1','P39V2.9', 'IK52551B_03_P2.3_FT', '11/25/2008','11/25/2008','UTAC','IFLEX-99',null,'LQFP',5479,5479,5479,0,0,0,0,0,0,0,0,0,0,0,0,0,0,null,0,0.00,0,0,0,0,null,'32',null,null,null,0
ALTER PROC SP_CREATE_UPDATE_TEST_SOFT_HARD_BIN_INFO
(
@PART_NUMBER	VARCHAR(50),
@LOT_NUMBER		VARCHAR(50),
@FAB_LOT_NUMBER VARCHAR(50),
@TEST_PROGRAM	VARCHAR(50),
@DATE_IN		DATETIME,
@DATE_OUT		DATETIME,
@VENDOR_NAME	VARCHAR(50),
@TESTER_ID		VARCHAR(50),
@DATE_CODE		VARCHAR(50),
@PKG_TYPE		VARCHAR(50),
@IN_QTY 		INT,
@GOOD_DIE_QTY	INT,
@Bin_1			INT, 
@Bin_2			INT, 
@Bin_3			INT, 
@Bin_4			INT, 
@Bin_5			INT, 
@Bin_6			INT, 
@Bin_7			INT, 
@Bin_8			INT, 
@Bin_9			INT, 
@Bin_10			INT,
@Bin_11			INT,
@Bin_12			INT,
@Bin_13			INT,
@Bin_14			INT,
@Bin_15			INT,
@HANDLER		VARCHAR(50),
@OS_QTY			INT,
@OS_RATE		FLOAT,
@DAMAGE_QTY		INT,
@LOSS_QTY       INT,
@SCANIN_QTY		INT,
@SCANOUT_QTY	INT,
@PO_NUMBER		VARCHAR(50),
@LEAD_COUNT		VARCHAR(50),
@LBID			VARCHAR(50),
@TEMPARATURE	VARCHAR(50),
@STAGE			VARCHAR(50),
@VM_QTY			INT,
@YIELD			FLOAT,
@DUT_BOARD_NO   VARCHAR(50),
@DUT_CABLE_NO   VARCHAR(50), 
@SoftBin_1 INT, @SoftBin_2 INT, @SoftBin_3 INT, @SoftBin_4 INT, @SoftBin_5 INT, @SoftBin_6 INT, @SoftBin_7 INT, @SoftBin_8 INT,	@SoftBin_9 INT, @SoftBin_10 INT,
@SoftBin_11 INT,@SoftBin_12 INT,@SoftBin_13 INT,@SoftBin_14 INT,@SoftBin_15 INT,@SoftBin_16 INT,@SoftBin_17 INT,@SoftBin_18 INT,@SoftBin_19 INT,@SoftBin_20 INT,
@SoftBin_21 INT,@SoftBin_22 INT,@SoftBin_23 INT,@SoftBin_24 INT,@SoftBin_25 INT,@SoftBin_26 INT,@SoftBin_27 INT,@SoftBin_28 INT,@SoftBin_29 INT,@SoftBin_30 INT,
@SoftBin_31 INT,@SoftBin_32 INT,@SoftBin_33 INT,@SoftBin_34 INT,@SoftBin_35 INT,@SoftBin_36 INT,@SoftBin_37 INT,@SoftBin_38 INT,@SoftBin_39 INT,@SoftBin_40 INT 
)
AS
BEGIN
		
	DECLARE @LOT_ID			INT
	DECLARE @Bin_Rejected	INT
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @LOCATION_ID	INT
	DECLARE @TARGET_YIELD FLOAT

	IF @YIELD IS NULL OR @YIELD='' OR LEN(RTRIM(LTRIM(@YIELD))) = 0
	BEGIN
		SET @YIELD = (CAST(@GOOD_DIE_QTY AS FLOAT) * 100 / CAST(@IN_QTY AS FLOAT) ) 
	END
	SET @Bin_Rejected = @IN_QTY - @GOOD_DIE_QTY 
			
	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'Test' AND PS_TYPE = 'FT' AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Test' AND L_VENDOR_NAME = @VENDOR_NAME	   	
   	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
   	BEGIN
   			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME='Test' AND PS_TYPE = 'FT' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
			IF ( @PROCESS_STEP_ID IS NULL OR @LOCATION_ID IS NULL )
			BEGIN
				--For Discrepancy Lot Notification Alert
				IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'FT' AND IS_USER_NOTIFIED = 1 ) = 0 
				BEGIN
					INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
						VALUES ( @part_number, @lot_number, @FAB_LOT_NUMBER, @vendor_name, 'FT','Discrepancy Lot Notification', null, @IN_QTY, null, 'Open', CURRENT_TIMESTAMP )
				END
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				RETURN
			END
	END
	
	SELECT @TARGET_YIELD = ISNULL(Parameter_Value, 92) FROM PROCESS_PARAMETER WHERE ProcessStep_Id = @PROCESS_STEP_ID AND Parameter_Key = 'Target Yield'
	IF @TARGET_YIELD = '' OR @TARGET_YIELD = '0.00'
	BEGIN
		SET @TARGET_YIELD = CAST( 92  AS FLOAT )
	END

	IF @YIELD < @TARGET_YIELD AND @GOOD_DIE_QTY > 500
	BEGIN
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND Notification_Name = 'FT Low Yield Notification' AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'FT' AND  IS_USER_NOTIFIED = 1 ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty,Qty_out, Yield, Status,TimeStamp )
				VALUES ( @part_number, @lot_number, @FAB_LOT_NUMBER, @vendor_name, 'FT','FT Low Yield Notification', @IN_QTY, @GOOD_DIE_QTY, @YIELD,'Open', CURRENT_TIMESTAMP )
		END
	END

	SELECT @lot_id = Lot_ID FROM Lot_Table WHERE ProcessStep_ID = @process_step_id AND Location_ID = @location_id AND Lot_Number = @lot_number	
	
   	IF @lot_id IS NULL -- Check if the lot exists
   	BEGIN -- Create Lot
   	
   		insert into lot_table (processstep_id, location_id, lot_number, fab_lot_number, vendor_lot_number, date_in, qty_in, Qty_Out, Date_Out, lot_current_status,Good_Die_Qty, timestamp, PO_NUMBER , lot_entered_by, lot_category, Lot_Drop_Out) 
			values (@process_step_id, @location_id, @lot_number, @FAB_LOT_NUMBER, @lot_number, @date_in, @IN_QTY, @GOOD_DIE_QTY, IsNUll(@DATE_OUT,current_timestamp) , 'Close', @GOOD_DIE_QTY, current_timestamp,@PO_NUMBER, 1 , 'PROD', 0 )
   		SELECT @lot_id = @@IDENTITY
		
   		-- Insert Final_Test_Lot
		-- SELECT * FROM Final_Test_Lot
   		INSERT INTO Final_Test_Lot (Lot_ID, FTL_Package_Type, FTL_Lead_Count, FTL_Test_Program, TESTER_ID, DATE_CODE, HANDLER, OS_QTY, OS_RATE, DAMAGE_QTY, LOSS_QTY, LB_ID, ROOM_TEMP, STAGE, VM_QTY, TIMESTAMP) 
					VALUES (@lot_id, @PKG_TYPE, @LEAD_COUNT, @TEST_PROGRAM, @TESTER_ID, @DATE_CODE, @HANDLER, @OS_QTY, @OS_RATE, @DAMAGE_QTY, @LOSS_QTY, @LBID, @TEMPARATURE, @STAGE, @VM_QTY, CURRENT_TIMESTAMP)

		INSERT INTO Test_Informative_Binning (Lot_Id, Good_Dice, IN_QTY, Yield, Bin_1, Bin_2, Bin_3, Bin_4, Bin_5, Bin_6, Bin_7, Bin_8, Bin_9, Bin_10, Bin_11, Bin_12, Bin_13, Bin_14, Bin_15, Bin_Rejected, Timestamp,
										Package, LC, Test_Pgm, TESTER_ID, DATE_CODE, HANDLER, OS_QTY, OS_RATE, DAMAGE_QTY, LOSS_QTY,LB_ID, ROOM_TEMP, STAGE, VM_QTY,OUT_DATE ,Dut_Board_No, Dut_Cable_No) 
							VALUES (@lot_id, @GOOD_DIE_QTY, @IN_QTY, @YIELD, @Bin_1, @Bin_2, @Bin_3, @Bin_4, @Bin_5, @Bin_6, @Bin_7, @Bin_8, @Bin_9, @Bin_10, @Bin_11, @Bin_12, @Bin_13, @Bin_14, @Bin_15, @Bin_Rejected, CURRENT_TIMESTAMP,
							@PKG_TYPE, @LEAD_COUNT, @TEST_PROGRAM, @TESTER_ID, @DATE_CODE, @HANDLER, @OS_QTY, @OS_RATE, @DAMAGE_QTY, @LOSS_QTY, @LBID, @TEMPARATURE, @STAGE, @VM_QTY, IsNUll(@DATE_OUT,current_timestamp),@DUT_BOARD_NO, @DUT_CABLE_NO )

		INSERT INTO Test_Soft_Binning_Info (Lot_Id, Dut_Board_No, Dut_Cable_No,SoftBin_1, SoftBin_2, SoftBin_3, SoftBin_4, SoftBin_5, SoftBin_6, SoftBin_7, SoftBin_8, SoftBin_9, SoftBin_10, SoftBin_11, SoftBin_12, SoftBin_13, SoftBin_14, SoftBin_15, SoftBin_16, SoftBin_17, SoftBin_18, SoftBin_19, SoftBin_20,
			SoftBin_21, SoftBin_22, SoftBin_23, SoftBin_24, SoftBin_25, SoftBin_26, SoftBin_27, SoftBin_28, SoftBin_29, SoftBin_30, SoftBin_31, SoftBin_32, SoftBin_33, SoftBin_34, SoftBin_35, SoftBin_36, SoftBin_37, SoftBin_38, SoftBin_39, SoftBin_40, Timestamp) 
			VALUES (@LOT_ID, @DUT_BOARD_NO, @DUT_CABLE_NO,@SoftBin_1, @SoftBin_2, @SoftBin_3, @SoftBin_4, @SoftBin_5, @SoftBin_6, @SoftBin_7, @SoftBin_8, @SoftBin_9, @SoftBin_10, @SoftBin_11, @SoftBin_12, @SoftBin_13, @SoftBin_14, @SoftBin_15, @SoftBin_16, @SoftBin_17, @SoftBin_18, @SoftBin_19, @SoftBin_20, 
			@SoftBin_21, @SoftBin_22, @SoftBin_23, @SoftBin_24, @SoftBin_25, @SoftBin_26, @SoftBin_27, @SoftBin_28, @SoftBin_29, @SoftBin_30, @SoftBin_31, @SoftBin_32, @SoftBin_33, @SoftBin_34, @SoftBin_35, @SoftBin_36, @SoftBin_37, @SoftBin_38, @SoftBin_39, @SoftBin_40,CURRENT_TIMESTAMP)
   	END
   	ELSE 
   	BEGIN
   		-- UPDATE LOT TABLE
		update Lot_Table set lot_current_status = 'Close', QTY_OUT= @GOOD_DIE_QTY, date_out = IsNUll(@DATE_OUT,current_timestamp), Good_Die_Qty = @GOOD_DIE_QTY, Lot_Drop_Out = 0, PO_NUMBER = @PO_NUMBER where Lot_ID = @lot_id
		
		IF ( (SELECT COUNT(*) FROM Final_Test_Lot WHERE Lot_ID = @lot_id ) = 0 ) --NEED TO INSERT
		BEGIN				
				INSERT INTO Final_Test_Lot (Lot_ID, FTL_Package_Type, FTL_Lead_Count, FTL_Test_Program, TESTER_ID, DATE_CODE, HANDLER, OS_QTY, OS_RATE, DAMAGE_QTY, LOSS_QTY, LB_ID, ROOM_TEMP, STAGE, VM_QTY, TIMESTAMP) 
					VALUES (@lot_id, @PKG_TYPE, @LEAD_COUNT, @TEST_PROGRAM, @TESTER_ID, @DATE_CODE, @HANDLER, @OS_QTY, @OS_RATE, @DAMAGE_QTY, @LOSS_QTY, @LBID, @TEMPARATURE, @STAGE, @VM_QTY, CURRENT_TIMESTAMP)
		END
		ELSE
		BEGIN
			UPDATE Final_Test_Lot SET FTL_Package_Type = @PKG_TYPE, FTL_Lead_Count = @LEAD_COUNT, FTL_Test_Program = @TEST_PROGRAM, TESTER_ID = @TESTER_ID, DATE_CODE = @DATE_CODE, HANDLER = @HANDLER, OS_QTY = @OS_QTY, 
				OS_RATE = @OS_RATE, DAMAGE_QTY = @DAMAGE_QTY, LOSS_QTY = @LOSS_QTY, LB_ID =@LBID, ROOM_TEMP =@TEMPARATURE, STAGE = @STAGE, VM_QTY = @VM_QTY, TIMESTAMP = CURRENT_TIMESTAMP  WHERE Lot_ID = @lot_id   	
		END
	END
	
	 IF ( (SELECT COUNT(*) FROM Test_Informative_Binning WHERE Lot_ID = @lot_id AND STAGE= ISNULL(@STAGE,'') ) = 0 ) --NEED TO INSERT
	 BEGIN			
			INSERT INTO Test_Informative_Binning (Lot_Id, Good_Dice, IN_QTY, IN_DATE, Yield, Bin_1, Bin_2, Bin_3, Bin_4, Bin_5, Bin_6, Bin_7, Bin_8, Bin_9, Bin_10, Bin_11, Bin_12, Bin_13, Bin_14, Bin_15, Bin_Rejected, Timestamp,
							Package, LC, Test_Pgm, TESTER_ID, DATE_CODE, HANDLER, OS_QTY, OS_RATE, DAMAGE_QTY, LOSS_QTY,LB_ID, ROOM_TEMP, STAGE, VM_QTY,OUT_DATE,Dut_Board_No, Dut_Cable_No) 
							VALUES (@lot_id, @GOOD_DIE_QTY, @IN_QTY, @DATE_IN, @YIELD, @Bin_1, @Bin_2, @Bin_3, @Bin_4, @Bin_5, @Bin_6, @Bin_7, @Bin_8, @Bin_9, @Bin_10, @Bin_11, @Bin_12, @Bin_13, @Bin_14, @Bin_15, @Bin_Rejected, CURRENT_TIMESTAMP,
							@PKG_TYPE, @LEAD_COUNT, @TEST_PROGRAM, @TESTER_ID, @DATE_CODE, @HANDLER, @OS_QTY, @OS_RATE, @DAMAGE_QTY, @LOSS_QTY, @LBID, @TEMPARATURE, ISNULL(@STAGE,''), @VM_QTY, IsNUll(@DATE_OUT,current_timestamp),@DUT_BOARD_NO, @DUT_CABLE_NO )
	 END
	 ELSE --NEED TO UPDATE
	 BEGIN
			UPDATE Test_Informative_Binning SET  Good_Dice = @GOOD_DIE_QTY, IN_QTY = @IN_QTY, IN_DATE = ISNULL(@DATE_IN,IN_DATE), Yield = @YIELD, 
				Bin_1 = @Bin_1,  Bin_2 = @Bin_2,  Bin_3 = @Bin_3,  Bin_4 = @Bin_4,  Bin_5 = @Bin_5,  Bin_6 = @Bin_6,  Bin_7 = @Bin_7,  Bin_8 = @Bin_8,  Bin_9 = @Bin_9,  Bin_10 = @Bin_10,  Bin_11 = @Bin_11,  Bin_12 = @Bin_12,  Bin_13 = @Bin_13,  Bin_14 = @Bin_14,  Bin_15 = @Bin_15,  Bin_Rejected = @Bin_Rejected,  Timestamp = CURRENT_TIMESTAMP,
				Package = @PKG_TYPE, LC = @LEAD_COUNT, Test_Pgm = @TEST_PROGRAM, TESTER_ID = @TESTER_ID, DATE_CODE = @DATE_CODE, HANDLER = @HANDLER, OS_QTY = @OS_QTY, 
				OS_RATE = @OS_RATE, DAMAGE_QTY = @DAMAGE_QTY, LOSS_QTY = @LOSS_QTY, LB_ID =@LBID, ROOM_TEMP =@TEMPARATURE, STAGE = ISNULL(@STAGE,''), VM_QTY = @VM_QTY, OUT_DATE =  IsNUll(@DATE_OUT,current_timestamp),
				Dut_Board_No = @DUT_BOARD_NO,  Dut_Cable_No = @DUT_CABLE_NO
				WHERE Lot_ID = @lot_id AND STAGE= IsNULL(@STAGE, '' )
	 END
	 IF ( (SELECT COUNT(*) FROM Test_Soft_Binning_Info WHERE Lot_ID = @lot_id ) = 0 ) --NEED TO INSERT
	 BEGIN	
		 INSERT INTO Test_Soft_Binning_Info (Lot_Id, Dut_Board_No, Dut_Cable_No,SoftBin_1, SoftBin_2, SoftBin_3, SoftBin_4, SoftBin_5, SoftBin_6, SoftBin_7, SoftBin_8, SoftBin_9, SoftBin_10, SoftBin_11, SoftBin_12, SoftBin_13, SoftBin_14, SoftBin_15, SoftBin_16, SoftBin_17, SoftBin_18, SoftBin_19, SoftBin_20,
			SoftBin_21, SoftBin_22, SoftBin_23, SoftBin_24, SoftBin_25, SoftBin_26, SoftBin_27, SoftBin_28, SoftBin_29, SoftBin_30, SoftBin_31, SoftBin_32, SoftBin_33, SoftBin_34, SoftBin_35, SoftBin_36, SoftBin_37, SoftBin_38, SoftBin_39, SoftBin_40, Timestamp) 
		VALUES (@LOT_ID, @DUT_BOARD_NO, @DUT_CABLE_NO, @SoftBin_1, @SoftBin_2, @SoftBin_3, @SoftBin_4, @SoftBin_5, @SoftBin_6, @SoftBin_7, @SoftBin_8, @SoftBin_9, @SoftBin_10, @SoftBin_11, @SoftBin_12, @SoftBin_13, @SoftBin_14, @SoftBin_15, @SoftBin_16, @SoftBin_17, @SoftBin_18, @SoftBin_19, @SoftBin_20, 
			@SoftBin_21, @SoftBin_22, @SoftBin_23, @SoftBin_24, @SoftBin_25, @SoftBin_26, @SoftBin_27, @SoftBin_28, @SoftBin_29, @SoftBin_30, @SoftBin_31, @SoftBin_32, @SoftBin_33, @SoftBin_34, @SoftBin_35, @SoftBin_36, @SoftBin_37, @SoftBin_38, @SoftBin_39, @SoftBin_40,CURRENT_TIMESTAMP)
	END
	ELSE
	BEGIN
		UPDATE Test_Soft_Binning_Info SET  Dut_Board_No = @DUT_BOARD_NO,  Dut_Cable_No = @DUT_CABLE_NO,
			SoftBin_1 = @SoftBin_1,  SoftBin_2 = @SoftBin_2,  SoftBin_3 = @SoftBin_3,  SoftBin_4 = @SoftBin_4,  SoftBin_5 = @SoftBin_5,  SoftBin_6 = @SoftBin_6,  SoftBin_7 = @SoftBin_7,  SoftBin_8 = @SoftBin_8,  SoftBin_9 = @SoftBin_9,  SoftBin_10 = @SoftBin_10,  SoftBin_11 = @SoftBin_11,  SoftBin_12 = @SoftBin_12,  SoftBin_13 = @SoftBin_13,  SoftBin_14 = @SoftBin_14,  SoftBin_15 = @SoftBin_15,  SoftBin_16 = @SoftBin_16,  SoftBin_17 = @SoftBin_17,  SoftBin_18 = @SoftBin_18,  SoftBin_19 = @SoftBin_19,  SoftBin_20 = @SoftBin_20,  
			SoftBin_21 = @SoftBin_21,  SoftBin_22 = @SoftBin_22,  SoftBin_23 = @SoftBin_23,  SoftBin_24 = @SoftBin_24,  SoftBin_25 = @SoftBin_25,  SoftBin_26 = @SoftBin_26,  SoftBin_27 = @SoftBin_27,  SoftBin_28 = @SoftBin_28,  SoftBin_29 = @SoftBin_29,  SoftBin_30 = @SoftBin_30,  SoftBin_31 = @SoftBin_31,  SoftBin_32 = @SoftBin_32,  SoftBin_33 = @SoftBin_33,  SoftBin_34 = @SoftBin_34,  SoftBin_35 = @SoftBin_35,  SoftBin_36 = @SoftBin_36,  SoftBin_37 = @SoftBin_37,  SoftBin_38 = @SoftBin_38,  SoftBin_39 = @SoftBin_39,  SoftBin_40 = @SoftBin_40,
			Timestamp = CURRENT_TIMESTAMP WHERE Lot_Id = @LOT_ID	
	END	
END							